﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;

using FU.SE0208.G1;

namespace FU.SE0208.G1.Controller
{
    public class OrderHandler
    {
        // Store procedure to get order
        private const string STR_DATA_ORDER = "Handler_Order";
        private const string STR_DATA_ORDER_DETAIL = "Handler_Order_Detail";

        public bool addNew(FU.SE0208.G1.Entities.Order obj)
        {
            SqlConnection conn = DbHandler.getConn();
            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand("AddOrder",conn);
                command.CommandType = CommandType.StoredProcedure;
                #region
                command.Parameters.Add(new SqlParameter("@ProID",SqlDbType.BigInt)).Value = Convert.ToInt64(obj.ProID1);
                command.Parameters.Add(new SqlParameter("@OderDate", SqlDbType.DateTime)).Value = obj.OrderDate1;
                command.Parameters.Add(new SqlParameter("@DelivDate", SqlDbType.DateTime)).Value = obj.DelivDate1;
                command.Parameters.Add(new SqlParameter("@DelivName", SqlDbType.NVarChar)).Value = obj.DelivName1;
                command.Parameters.Add(new SqlParameter("@DelivPhone", SqlDbType.NVarChar)).Value = obj.DelivPhone1;
                command.Parameters.Add(new SqlParameter("@DelivAdd", SqlDbType.NVarChar)).Value = obj.DelivName1;
                command.Parameters.Add(new SqlParameter("@OrderNote", SqlDbType.NVarChar)).Value = obj.OrderNote1;
                command.Parameters.Add(new SqlParameter("@OrderStatus", SqlDbType.NVarChar)).Value = obj.OrderStatus1;
                command.Parameters.Add(new SqlParameter("@Quantiy", SqlDbType.Int)).Value = obj.Quantity1;
                command.Parameters.Add(new SqlParameter("@Price", SqlDbType.Float)).Value = obj.Price1;
                command.Parameters.Add(new SqlParameter("@Tax", SqlDbType.Float)).Value = obj.Tax1;
                command.Parameters.Add(new SqlParameter("@CalUnit", SqlDbType.NVarChar)).Value = obj.CalUnit1;
                command.Parameters.Add(new SqlParameter("@EmpID", SqlDbType.Int)).Value = Convert.ToInt32(obj.EmpID1);
                #endregion
                int count = command.ExecuteNonQuery();
                if (count > 0)
                {
                    return true;
                }
                else
                    return false;
            }
            catch (Exception e) { return false; }
            finally { conn.Close(); conn.Dispose(); }
        }

        public bool del(FU.SE0208.G1.Entities.Order obj)
        {
            SqlConnection conn = DbHandler.getConn();
            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand("DeleteOrder",conn);
                command.CommandType = CommandType.StoredProcedure;
                command.Parameters.Add(new SqlParameter("@OrderID",SqlDbType.Int)).Value = Convert.ToInt32(obj.OrderID1);
                int count = command.ExecuteNonQuery();
                if (count > 0)
                {
                    return true;
                }
                else
                    return false;
            }
            catch (Exception e) { return false; }
            finally { conn.Close(); conn.Dispose(); }
        }

        public bool edit(FU.SE0208.G1.Entities.Order obj, FU.SE0208.G1.Entities.Order newObj)
        {
            SqlConnection conn = DbHandler.getConn();
            try
            {
                conn.Open();
                SqlCommand command = new SqlCommand("EditOrder",conn);
                command.CommandType = CommandType.StoredProcedure;
                #region
                command.Parameters.Add(new SqlParameter("@OrderID", SqlDbType.BigInt)).Value = Convert.ToInt64(obj.OrderID1);
                command.Parameters.Add(new SqlParameter("@ProID", SqlDbType.BigInt)).Value = Convert.ToInt64(newObj.ProID1);
                command.Parameters.Add(new SqlParameter("@OderDate", SqlDbType.DateTime)).Value = newObj.OrderDate1;
                command.Parameters.Add(new SqlParameter("@DelivDate", SqlDbType.DateTime)).Value = newObj.DelivDate1;
                command.Parameters.Add(new SqlParameter("@DelivName", SqlDbType.NVarChar)).Value = newObj.DelivName1;
                command.Parameters.Add(new SqlParameter("@DelivPhone", SqlDbType.NVarChar)).Value = newObj.DelivPhone1;
                command.Parameters.Add(new SqlParameter("@DelivAdd", SqlDbType.NVarChar)).Value = newObj.DelivName1;
                command.Parameters.Add(new SqlParameter("@OrderNote", SqlDbType.NVarChar)).Value = newObj.OrderNote1;
                command.Parameters.Add(new SqlParameter("@OrderStatus", SqlDbType.NVarChar)).Value = newObj.OrderStatus1;
                command.Parameters.Add(new SqlParameter("@Quantiy", SqlDbType.Int)).Value = newObj.Quantity1;
                command.Parameters.Add(new SqlParameter("@Price", SqlDbType.Float)).Value = newObj.Price1;
                command.Parameters.Add(new SqlParameter("@Tax", SqlDbType.Float)).Value = newObj.Tax1;
                command.Parameters.Add(new SqlParameter("@CalUnit", SqlDbType.NVarChar)).Value = newObj.CalUnit1;
                command.Parameters.Add(new SqlParameter("@EmpID", SqlDbType.Int)).Value = Convert.ToInt32(newObj.EmpID1);
                #endregion
                int count = command.ExecuteNonQuery();
                return true;
            }
            catch (Exception e) { return false; }
            finally { conn.Close(); conn.Dispose(); }
        }

        public bool getAllOrder(DataSet ds, string sTableOrder, string sTableOrderDetail)
        {
            // Load table order
            if (DbHandler.getQuery(ds, STR_DATA_ORDER, sTableOrder) == false)
            {
                return false;
            }

            //Load table order detail
            if (DbHandler.getQuery(ds, STR_DATA_ORDER_DETAIL, sTableOrderDetail) == false)
            {
                return false;
            }

            return true;
        }

        public bool delOrderByID(int iOrderId)
        {
            string sSQL1 = "DELETE FROM Order_Detail WHERE OrdId = " + iOrderId;
            if (DbHandler.doQuery(sSQL1) == false)
            {
                return false;
            }

            return true;
        }

        public bool delOrderDetailByID(int iOrderId)
        {
            string sSQL1 = "DELETE FROM Order_Detail WHERE OrdId = " + iOrderId;
            if (DbHandler.doQuery(sSQL1) == false)
            {
                return false;
            }

            return true;
        }

        public bool updateOrderDetailByID(int iOrderId, string sProId, int iQuantity)
        {
            string sSQL1 = "UPDATE Order_Detail SET Quantity = " + iQuantity + " WHERE OrdId = " + iOrderId + " and ProId = '" + sProId + "'";
            if (DbHandler.doQuery(sSQL1) == false)
            {
                return false;
            }

            return true;
        }
        public bool updateOrder()
        {
            //string sSQL1 = "INSERT INTO [Order] " +
            //               "([OrderDate]" +
            //               ",[DelivDate]" +
            //               ",[DelivName]" +
            //               ",[DelivPhone]" +
            //               ",[DelivAdd]" +
            //               ",[Note]" +
            //               ",[Status]" +
            //               ",[CusId]" +
            //               ",[EmpId]) " +
            //               "VALUES " +
            //               "('" + od + "'" +
            //               ",'" + dd + "'" +
            //               ",'" + dn + "'" +
            //               ",'" + dp + "'" +
            //               ",'" + da + "'" +
            //               ",'" + note + "'" +
            //               ",'" + status + "'" +
            //               "," + cusId +
            //               "," + empId + ")";
            //if (DbHandler.doQuery(sSQL1) == false)
            //{
            //    return false;
            //}

            return true;
        }


        public bool addOrder()
        {
            //string sSQL1 = "INSERT INTO [Order] " + 
            //               "([OrderDate]" +
            //               ",[DelivDate]" +
            //               ",[DelivName]" + 
            //               ",[DelivPhone]" +
            //               ",[DelivAdd]" +
            //               ",[Note]" +
            //               ",[Status]" +
            //               ",[CusId]" +
            //               ",[EmpId]) " +
            //               "VALUES " +
            //               "('" + od + "'" +
            //               ",'" + dd + "'" +
            //               ",'" + dn + "'" + 
            //               ",'" + dp + "'" +
            //               ",'" + da + "'" +
            //               ",'" + note + "'" +
            //               ",'" + status + "'" +
            //               "," + cusId +
            //               "," + empId + ")";

            //if (DbHandler.doQuery(sSQL1) == false)
            //{
            //    return false;
            //}

            return true;
        }

        public bool updateSerial(string sSerial)
        {
            //string sSQL1 = "INSERT INTO [Order] " + 
            //               "([OrderDate]" +
            //               ",[DelivDate]" +
            //               ",[DelivName]" + 
            //               ",[DelivPhone]" +
            //               ",[DelivAdd]" +
            //               ",[Note]" +
            //               ",[Status]" +
            //               ",[CusId]" +
            //               ",[EmpId]) " +
            //               "VALUES " +
            //               "('" + od + "'" +
            //               ",'" + dd + "'" +
            //               ",'" + dn + "'" + 
            //               ",'" + dp + "'" +
            //               ",'" + da + "'" +
            //               ",'" + note + "'" +
            //               ",'" + status + "'" +
            //               "," + cusId +
            //               "," + empId + ")";

            //if (DbHandler.doQuery(sSQL1) == false)
            //{
            //    return false;
            //}

            return true;
        }

        public bool addOrderDetailByID(int iOrderId, string sProId, int iQuantity)
        {
            string sSQL1 = "INSERT INTO Order_Detail(Quantity,ProId,OrdId) VALUES(" + iQuantity + ",'" + sProId + "'," + iOrderId + ")";
            if (DbHandler.doQuery(sSQL1) == false)
            {
                return false;
            }

            return true;
        }
    }
}
